package com.wande.dataplatform.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.wande.common.core.exception.ServiceException;
import com.wande.common.mybatis.core.page.PageQuery;
import com.wande.common.mybatis.core.page.TableDataInfo;
import com.wande.common.redis.utils.RedisUtils;
import com.wande.dataplatform.common.enums.DataPlatformErrorCode;
import com.wande.dataplatform.domain.DataSource;
import com.wande.dataplatform.domain.QueryHistory;
import com.wande.dataplatform.domain.bo.QueryHistoryBo;
import com.wande.dataplatform.domain.dto.QueryExecuteDTO;
import com.wande.dataplatform.domain.dto.QueryResultDTO;
import com.wande.dataplatform.domain.vo.QueryHistoryVO;
import com.wande.dataplatform.mapper.DataSourceMapper;
import com.wande.dataplatform.mapper.QueryHistoryMapper;
import com.wande.dataplatform.service.IDataQueryService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.io.ByteArrayOutputStream;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.time.Duration;
import java.util.*;

/**
 * 数据查询服务实现
 *
 * @author wande
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class DataQueryServiceImpl implements IDataQueryService {

    private final QueryHistoryMapper queryHistoryMapper;
    private final DataSourceMapper dataSourceMapper;

    private static final String CACHE_KEY_PREFIX = "data_query:";
    private static final Duration CACHE_DURATION = Duration.ofMinutes(10);
    private static final int MAX_EXPORT_ROWS = 10000;

    @Override
    public QueryResultDTO executeQuery(QueryExecuteDTO dto) {
        long startTime = System.currentTimeMillis();
        QueryResultDTO result = new QueryResultDTO();
        QueryHistory history = new QueryHistory();

        try {
            // 获取数据源
            DataSource dataSource = dataSourceMapper.selectById(dto.getDatasourceId());
            if (dataSource == null) {
                throw new ServiceException(DataPlatformErrorCode.DATASOURCE_NOT_FOUND.getMessage());
            }

            // 检查缓存
            String cacheKey = generateCacheKey(dto);
            QueryResultDTO cachedResult = RedisUtils.getCacheObject(cacheKey);
            if (cachedResult != null) {
                log.info("从缓存获取查询结果: {}", cacheKey);
                return cachedResult;
            }

            // 执行查询
            result = executeQueryInternal(dataSource, dto);
            result.setExecutionTime(System.currentTimeMillis() - startTime);

            // 缓存结果
            RedisUtils.setCacheObject(cacheKey, result, CACHE_DURATION);

            // 保存历史记录
            if (Boolean.TRUE.equals(dto.getSaveHistory())) {
                history.setQueryName(dto.getQueryName());
                history.setQueryType(dto.getQueryType());
                history.setSqlContent(dto.getSqlContent());
                history.setQueryConfig(dto.getQueryConfig());
                history.setDatasourceId(dto.getDatasourceId());
                history.setExecutionTime(result.getExecutionTime());
                history.setResultCount(result.getRows() != null ? result.getRows().size() : 0);
                history.setStatus("SUCCESS");
                history.setProjectId(dto.getProjectId());
                queryHistoryMapper.insert(history);
                result.setHistoryId(history.getId());
            }

            return result;

        } catch (Exception e) {
            log.error("查询执行失败", e);
            
            // 保存失败记录
            if (Boolean.TRUE.equals(dto.getSaveHistory())) {
                history.setQueryName(dto.getQueryName());
                history.setQueryType(dto.getQueryType());
                history.setSqlContent(dto.getSqlContent());
                history.setQueryConfig(dto.getQueryConfig());
                history.setDatasourceId(dto.getDatasourceId());
                history.setExecutionTime(System.currentTimeMillis() - startTime);
                history.setStatus("FAILED");
                history.setErrorMessage(e.getMessage());
                history.setProjectId(dto.getProjectId());
                queryHistoryMapper.insert(history);
            }

            throw new ServiceException("查询执行失败: " + e.getMessage());
        }
    }

    @Override
    public byte[] exportQueryResult(QueryExecuteDTO dto, String format) {
        try {
            // 设置大页码以获取所有数据
            dto.setPageNum(1);
            dto.setPageSize(MAX_EXPORT_ROWS);
            dto.setSaveHistory(false); // 导出不保存历史

            QueryResultDTO result = executeQuery(dto);

            return switch (format.toUpperCase()) {
                case "CSV" -> exportToCsv(result);
                case "JSON" -> exportToJson(result);
                case "EXCEL" -> exportToExcel(result);
                default -> throw new ServiceException("不支持的导出格式: " + format);
            };

        } catch (Exception e) {
            log.error("导出查询结果失败", e);
            throw new ServiceException("导出失败: " + e.getMessage());
        }
    }

    @Override
    public TableDataInfo<QueryHistoryVO> queryHistoryList(QueryHistoryBo bo, PageQuery pageQuery) {
        LambdaQueryWrapper<QueryHistory> wrapper = buildQueryWrapper(bo);
        Page<QueryHistoryVO> page = queryHistoryMapper.selectVoPage(pageQuery.build(), wrapper);
        return TableDataInfo.build(page);
    }

    @Override
    public QueryHistoryVO queryHistoryById(Long id) {
        return queryHistoryMapper.selectVoById(id);
    }

    @Override
    public Boolean deleteHistoryByIds(Long[] ids) {
        return queryHistoryMapper.deleteByIds(Arrays.asList(ids)) > 0;
    }

    @Override
    public QueryResultDTO reExecuteHistory(Long id) {
        QueryHistory history = queryHistoryMapper.selectById(id);
        if (history == null) {
            throw new ServiceException("查询历史不存在");
        }

        QueryExecuteDTO dto = new QueryExecuteDTO();
        dto.setQueryName(history.getQueryName());
        dto.setQueryType(history.getQueryType());
        dto.setSqlContent(history.getSqlContent());
        dto.setQueryConfig(history.getQueryConfig());
        dto.setDatasourceId(history.getDatasourceId());
        dto.setProjectId(history.getProjectId());
        dto.setSaveHistory(true);

        return executeQuery(dto);
    }

    /**
     * 执行查询内部方法
     */
    private QueryResultDTO executeQueryInternal(DataSource dataSource, QueryExecuteDTO dto) throws SQLException {
        QueryResultDTO result = new QueryResultDTO();
        
        String jdbcUrl = buildJdbcUrl(dataSource);
        String username = dataSource.getUsername();
        String password = dataSource.getPassword(); // TODO: 解密

        try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
             Statement stmt = conn.createStatement()) {

            // 设置查询超时
            stmt.setQueryTimeout(60);

            // 执行查询
            String sql = dto.getSqlContent();
            
            // 添加分页
            if (dto.getPageNum() != null && dto.getPageSize() != null) {
                int offset = (dto.getPageNum() - 1) * dto.getPageSize();
                sql = addPagination(sql, offset, dto.getPageSize(), dataSource.getType());
            }

            try (ResultSet rs = stmt.executeQuery(sql)) {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();

                // 构建列信息
                List<QueryResultDTO.ColumnInfo> columns = new ArrayList<>();
                for (int i = 1; i <= columnCount; i++) {
                    QueryResultDTO.ColumnInfo column = new QueryResultDTO.ColumnInfo();
                    column.setName(metaData.getColumnLabel(i));
                    column.setType(metaData.getColumnTypeName(i));
                    column.setComment(metaData.getColumnLabel(i));
                    columns.add(column);
                }
                result.setColumns(columns);

                // 读取数据行
                List<Map<String, Object>> rows = new ArrayList<>();
                while (rs.next()) {
                    Map<String, Object> row = new LinkedHashMap<>();
                    for (int i = 1; i <= columnCount; i++) {
                        row.put(metaData.getColumnLabel(i), rs.getObject(i));
                    }
                    rows.add(row);
                }
                result.setRows(rows);
                result.setTotal((long) rows.size());
            }
        }

        return result;
    }

    /**
     * 构建JDBC URL
     */
    private String buildJdbcUrl(DataSource dataSource) {
        return switch (dataSource.getType()) {
            case "MYSQL" -> String.format("jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf8&useSSL=false",
                    dataSource.getHost(), dataSource.getPort(), dataSource.getDatabaseName());
            case "ORACLE" -> String.format("jdbc:oracle:thin:@%s:%d:%s",
                    dataSource.getHost(), dataSource.getPort(), dataSource.getDatabaseName());
            case "POSTGRESQL" -> String.format("jdbc:postgresql://%s:%d/%s",
                    dataSource.getHost(), dataSource.getPort(), dataSource.getDatabaseName());
            case "DORIS" -> String.format("jdbc:mysql://%s:%d/%s",
                    dataSource.getHost(), dataSource.getPort(), dataSource.getDatabaseName());
            default -> throw new ServiceException("不支持的数据源类型: " + dataSource.getType());
        };
    }

    /**
     * 添加分页SQL
     */
    private String addPagination(String sql, int offset, int limit, String dbType) {
        return switch (dbType) {
            case "MYSQL", "DORIS" -> sql + String.format(" LIMIT %d, %d", offset, limit);
            case "ORACLE" -> String.format(
                    "SELECT * FROM (SELECT ROWNUM RN, T.* FROM (%s) T WHERE ROWNUM <= %d) WHERE RN > %d",
                    sql, offset + limit, offset);
            case "POSTGRESQL" -> sql + String.format(" LIMIT %d OFFSET %d", limit, offset);
            default -> sql;
        };
    }

    /**
     * 导出为CSV
     */
    private byte[] exportToCsv(QueryResultDTO result) {
        try (ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
            // 写入列头
            List<String> headers = result.getColumns().stream()
                    .map(QueryResultDTO.ColumnInfo::getName)
                    .toList();
            baos.write(String.join(",", headers).getBytes(StandardCharsets.UTF_8));
            baos.write("\n".getBytes(StandardCharsets.UTF_8));

            // 写入数据行
            for (Map<String, Object> row : result.getRows()) {
                List<String> values = new ArrayList<>();
                for (QueryResultDTO.ColumnInfo column : result.getColumns()) {
                    Object value = row.get(column.getName());
                    values.add(value != null ? value.toString() : "");
                }
                baos.write(String.join(",", values).getBytes(StandardCharsets.UTF_8));
                baos.write("\n".getBytes(StandardCharsets.UTF_8));
            }

            return baos.toByteArray();
        } catch (Exception e) {
            throw new ServiceException("导出CSV失败: " + e.getMessage());
        }
    }

    /**
     * 导出为JSON
     */
    private byte[] exportToJson(QueryResultDTO result) {
        return JSONUtil.toJsonStr(result.getRows()).getBytes(StandardCharsets.UTF_8);
    }

    /**
     * 导出为Excel
     */
    private byte[] exportToExcel(QueryResultDTO result) {
        // TODO: 使用FastExcel实现Excel导出
        throw new ServiceException("Excel导出功能待实现");
    }

    /**
     * 生成缓存键
     */
    private String generateCacheKey(QueryExecuteDTO dto) {
        return CACHE_KEY_PREFIX + 
               dto.getDatasourceId() + ":" + 
               dto.getSqlContent().hashCode() + ":" +
               dto.getPageNum() + ":" +
               dto.getPageSize();
    }

    /**
     * 构建查询条件
     */
    private LambdaQueryWrapper<QueryHistory> buildQueryWrapper(QueryHistoryBo bo) {
        LambdaQueryWrapper<QueryHistory> wrapper = Wrappers.lambdaQuery();
        wrapper.like(StrUtil.isNotBlank(bo.getQueryName()), QueryHistory::getQueryName, bo.getQueryName())
               .eq(bo.getDatasourceId() != null, QueryHistory::getDatasourceId, bo.getDatasourceId())
               .eq(bo.getProjectId() != null, QueryHistory::getProjectId, bo.getProjectId())
               .orderByDesc(QueryHistory::getCreateTime);
        return wrapper;
    }
}
